package com.hedl.practice.diyJava.jdbc;

import java.sql.*;

public class Client {
    private static final String Url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
    private static final String UserName = "root";
    private static final String PassWord = "shupian226";

    private Connection getConnection() throws SQLException {
        return DriverManager.getConnection(Url,UserName,PassWord);
    }

    //关闭数据库连接
    private void close(Connection conn, Statement stmt, ResultSet rs) throws SQLException{
        if (rs != null){
            rs.close();
        }
        if (stmt != null){
            stmt.close();
        }
        if (conn != null){
            conn.close();
        }
    }

    //执行增，删，改操作
    public int executeUpdate(String sql,Object[] params) throws SQLException{
        Connection conn = null;
        PreparedStatement stmt = null;
        int rows = 0;
        try{
            conn = getConnection();
            stmt = conn.prepareStatement(sql);

            //设置参数
            if (params != null){
                for (int i = 0;i < params.length;i++){
                    stmt.setObject(i + 1,params[i]);
                }
            }
            //执行SQL语句
            rows = stmt.executeUpdate();
        }finally {
            close(conn,stmt,null);
        }
        return rows;
    }

    //执行查询操作
    public ResultSet executeQuery(String sql,Object[] params) throws SQLException{
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;

        try{
            conn = getConnection();
            stmt = conn.prepareStatement(sql);
            //设置参数
            if (params != null){
                for (int i = 0;i < params.length;i++){
                    stmt.setObject(i + 1,params[i]);
                }
            }
            //执行SQL语句
            rs = stmt.executeQuery();
        }catch (SQLException e){
            close(conn,stmt,rs);
            throw e;
        }
        return rs;
    }

    //插入数据到表中
    public int insert(String tableName,String[] columns,Object[] values) throws SQLException{
        if (columns == null || values == null || columns.length != values.length){
            throw new IllegalArgumentException("invalid columns or values");
        }
        //构造SQL语句
        StringBuilder sqlBuilder = new StringBuilder("INSERT INTO");
        sqlBuilder.append(tableName).append("(");
        for (int i = 0;i < columns.length;i++){
            sqlBuilder.append(columns[i]);
            if (i < columns.length - 1){
                sqlBuilder.append(",");
            }
        }
        sqlBuilder.append(")VALUES(");
        for (int i = 0;i < values.length;i++){
            sqlBuilder.append("?");
            if (i < values.length - 1){
                sqlBuilder.append(",");
            }
        }
        sqlBuilder.append(")");
        //执行SQL语句
        return executeUpdate(sqlBuilder.toString(),values);
    }

    //更新表中的数据
    public int update(String tableName,String[] columns,Object[] values,String whereClause,Object[] whereArgs) throws SQLException{
        if (columns == null || values == null || columns.length != values.length){
            throw new IllegalArgumentException("invalid columns or values");
        }
        //构造SQL语句
        StringBuilder sqlBuilder = new StringBuilder("UPDATE");
        sqlBuilder.append(tableName).append("SET");
        for (int i = 0;i < columns.length;i++){
            sqlBuilder.append(columns[i]).append("=?");
            if (i < columns.length - 1){
                sqlBuilder.append(",");
            }
        }
        if (whereClause != null && whereClause.length() > 0){
            sqlBuilder.append("WHERE").append(whereClause);
        }
        //合并参数
        Object[] params = new Object[values.length + (whereArgs != null ? whereArgs.length : 0)];
        System.arraycopy(values,0,params,0,values.length);
        if (whereArgs != null){
            System.arraycopy(whereArgs,0,params,values.length,whereArgs.length);
        }
        //执行SQL语句
        return executeUpdate(sqlBuilder.toString(),params);
    }

    //删除表中的数据
    public int delete(String tableName,String whereClause,Object[] whereArgs) throws SQLException{
        //构造SQL语句
        StringBuilder sqlBuilder = new StringBuilder("DELETE FROM");
        sqlBuilder.append(tableName);
        if (whereClause != null && whereClause.length() > 0){
            sqlBuilder.append("WHERE").append(whereClause);
        }
        //执行SQL语句
        return executeUpdate(sqlBuilder.toString(),whereArgs);
    }

    //查询表中数据
    public ResultSet query(String tableName,String[] columns,String selection,Object[] selectionArgs,String groupBy,String having,String orderBy,String limit) throws SQLException{
        //构造SQL语句
        StringBuilder sqlBuilder = new StringBuilder("SELECT");
        if (columns != null && columns.length > 0){
            for (int i = 0;i < columns.length;i++){
                sqlBuilder.append(columns[i]);
                if (i < columns.length - 1){
                    sqlBuilder.append(",");
                }
            }
        }else{
            sqlBuilder.append("*");
        }
        sqlBuilder.append("FROM").append(tableName);
        if (selection != null && selection.length() > 0){
            sqlBuilder.append("WHERE").append(selection);
        }
        if (groupBy != null && groupBy.length() > 0){
            sqlBuilder.append("GROUP BY").append(groupBy);
        }
        if (having != null && having.length() > 0){
            sqlBuilder.append("HAVING").append(having);
        }
        if (orderBy != null && orderBy.length() > 0){
            sqlBuilder.append("ORDER BY").append(orderBy);
        }
        if (limit != null && limit.length() > 0){
            sqlBuilder.append("LIMIT").append(limit);
        }
        //执行SQL语句
        return executeQuery(sqlBuilder.toString(),selectionArgs);
    }

    public static void main(String[] args) throws SQLException{
        Client client = new Client();

        try{
            //插入数据
            String[] columns = {"name","age"};
            Object[] values = {"Tom",20};
            client.insert("mytable",columns,values);

            //更新数据
            String[] updateColumns = {"age"};
            Object[] updateValues = {21};
            String whereClause = "name = ?";
            Object[] whereArgs = {"Tom"};
            client.update("mytable",updateColumns,updateValues,whereClause,whereArgs);

            //删除数据
            String deleteWhereClause = "age > ?";
            Object[] deleteWhereArgs = {20};
            client.delete("mytable",deleteWhereClause,deleteWhereArgs);

            //查询数据
            String[] queryColumns = {"name","age"};
            String selection = "age > ?";
            Object[] selectionArgs = {18};
            String groupBy = null;
            String having = null;
            String orderBy = "age DESC";
            String limit = "10";
            ResultSet rs = client.query("mytable",queryColumns,selection,selectionArgs,groupBy,having,orderBy,limit);

            //处理查询结果
            while(rs.next()){
                String name = rs.getString("name");
                int age = rs.getInt("age");
                System.out.println("Name: "+name+",Age: "+age);
            }
        }finally {
            client.close(null,null,null);
        }
    }
}
